package uit.jee.lib.access.mapper;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import uit.jee.lib.dto.BookDTO;
import uit.jee.lib.dto.CommentDTO;
import uit.jee.lib.dto.ReaderDTO;
/**
 *
 * @author ngloc_it
 */
public class CommentMapper extends MapperDB{
    private final String sqlQuery = "Select booklibrary.comment.Macomment, "
            + "booklibrary.comment.MaDocGia, "
            + "booklibrary.comment.MaSach, "
            + "booklibrary.comment.NoiDungCM, "
            + "booklibrary.comment.NgayCM, "
            + "booklibrary.docgia.TenDocGia "
            + "From booklibrary.docgia, booklibrary.comment "
            + " Where booklibrary.comment.MaDocGia = booklibrary.docgia.MaDocGia";

            //h, docgia.TenDocGia
//from booklibrary.comment, docgia
//where  booklibrary.comment.MaDocGia = booklibrary.docgia.MaDocGia
//And  booklibrary.comment.MaSach='SCHO'

    public CommentMapper() throws Exception {
        super();
    }

    private void InitCommentDTOFromRs(CommentDTO comment, ResultSet rs) throws Exception{
        if((rs != null) && (comment != null)){
            comment.setCommentId(rs.getString(CommentDTO.COMMENT_ID));
            comment.setBookId(rs.getString(CommentDTO.COMMENT_BOOK_ID));
            comment.setReaderId(rs.getString(CommentDTO.COMMENT_READER_ID));
            comment.setContents(rs.getString(CommentDTO.COMMENT_CONTENTS));
            comment.setDate(rs.getString(CommentDTO.COMMENT_DATE));
            comment.setReaderName(rs.getString(ReaderDTO.READER_NAME));
        }
    }

    public boolean CheckExistedComment(CommentDTO comment) throws Exception {
        boolean isExisted = false;

        try {
            StringBuffer sql = new StringBuffer();
            sql.append("Select * from booklibrary.comment where ");
            sql.append(" booklibrary.comment.");
            sql.append(CommentDTO.COMMENT_ID).append(" = '");
            sql.append(comment.getCommentId()).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet rs = stmt.executeQuery();
            if ((rs != null) && (rs.next())) {
                isExisted = true;
            }
        } catch (Exception ex) {
            throw ex;
        }

        return isExisted;
    }

    public ArrayList<CommentDTO> GetAllComment() throws Exception {
        ArrayList<CommentDTO> comments = new ArrayList<CommentDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(sqlQuery);
            PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmts.executeQuery();

            while (re != null && re.next()) {
                CommentDTO comment = new CommentDTO();
                InitCommentDTOFromRs(comment, re);
                comments.add(comment);
            }
        } catch (Exception ex) {
            throw ex;
        }
        return comments;
    }

    public ArrayList<CommentDTO> GetCommentOnBook(String bookId) throws Exception {
        ArrayList<CommentDTO> comments = new ArrayList<CommentDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(sqlQuery);
            sql.append(" And ");
            sql.append(" booklibrary.comment.");
            sql.append(CommentDTO.COMMENT_BOOK_ID).append("='").append(bookId).append("'");


            PreparedStatement stmts = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmts.executeQuery();

            while (re != null && re.next()) {
                CommentDTO comment = new CommentDTO();
                InitCommentDTOFromRs(comment, re);
                comments.add(comment);
            }
        } catch (Exception ex) {
            throw ex;
        }
        return comments;
    }

    public ArrayList<CommentDTO> GetCommentOnBook(BookDTO book) throws Exception {
        ArrayList<CommentDTO> comments = new ArrayList<CommentDTO>();
        try {
            StringBuffer sql = new StringBuffer();
            sql.append(sqlQuery);
            sql.append(" And ");
            sql.append(" booklibrary.comment.");
            sql.append(CommentDTO.COMMENT_BOOK_ID).append("='").append(book.getBookId()).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            ResultSet re = stmt.executeQuery();

            while (re != null && re.next()) {
                CommentDTO comment = new CommentDTO();
                InitCommentDTOFromRs(comment, re);
                comments.add(comment);
            }
        } catch (Exception ex) {
            throw ex;
        }
        return comments;
    }

    public void Insert(CommentDTO comment) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("Insert into booklibrary.comment values( ");
            sql.append("'").append(comment.getCommentId()).append("', ");
            sql.append("'").append(comment.getBookId()).append("', ");
            sql.append("'").append(comment.getReaderId()).append("', ");
            sql.append("'").append(comment.getContents()).append("', '");
            sql.append(comment.getDate()).append("')");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        } catch (Exception e) {
            throw e;
        }
    }

    public void Insert(CommentDTO[] comments) throws Exception {
    }

    public void Update(CommentDTO comment) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("Update booklibrary.comment Set ");
            sql.append(CommentDTO.COMMENT_ID).append("='").append(comment.getCommentId()).append("', ");
            sql.append(CommentDTO.COMMENT_BOOK_ID).append("='").append(comment.getBookId()).append("', ");
            sql.append(CommentDTO.COMMENT_READER_ID).append("='").append(comment.getReaderId()).append("', ");
            sql.append(CommentDTO.COMMENT_CONTENTS).append("='").append(comment.getContents()).append("', ");
            sql.append(CommentDTO.COMMENT_DATE).append("='").append(comment.getDate()).append("'");

            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
        } catch (Exception ex) {
            throw ex;
        }
    }

    public void Update(CommentDTO[] comments) throws Exception {
    }

    public void Delete(CommentDTO comment) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("delete * from booklibrary.comment where ");
            sql.append(CommentDTO.COMMENT_ID).append(" ='").append(comment.getCommentId()).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        } catch (Exception ex) {
            throw ex;
        }
    }

    public void Delete(CommentDTO[] comments) throws Exception {
    }

    public void Delete(String commentId) throws Exception {
        try {
            StringBuffer sql = new StringBuffer();
            sql.append("delete * from booklibrary.comment where ");
            sql.append(CommentDTO.COMMENT_ID).append(" ='").append(commentId).append("'");
            PreparedStatement stmt = getConnection().prepareStatement(sql.toString());
            stmt.execute();
            stmt.close();
        } catch (Exception ex) {
            throw ex;
        }
    }
}
